package com.vecspace.middleplatform.filemanagment.dao;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import com.vecspace.data.model.FilterColumn;
import com.vecspace.data.model.Pagination;
import com.vecspace.data.model.RowMapper;
import com.vecspace.database.dao.AbstractDao;
import com.vecspace.database.model.IDataRowMapper;
import com.vecspace.database.model.QueryResult;
import com.vecspace.database.model.QuerySQL;
import com.vecspace.middleplatform.filemanagment.pojo.UploadFileStorage;
import com.vecspace.utils.UUIDUtil;

public class UploadFileStorageDao extends AbstractDao{

	private static final String SelectFields = "ID,STORAGE_NAME,STORAGE_PATH,STORAGE_SIZE,USABLE_STATUS"
			+ ",REMARKS,DISPLAY_ORDINAL,RECORD_CREATE_USER,RECORD_UPDATE_USER,RECORD_CREATE_TIME,RECORD_UPDATE_TIME,RECORD_EDIT_FLAG";

	public UploadFileStorage getUploadFileStorage(String id) throws Exception {
		String sql = "select " + SelectFields + " from TMS_UPLOAD_FILE_STORAGE where id=?";
		Map<String, Object> row = dao.queryDataRowMap(sql, new Object[] {id});
		UploadFileStorage fileStorage = this.parseFrom(row);
		return fileStorage;
	}

	public void deleteUploadFileStorage(String id) throws Exception {
		if (id != null && id.length() > 0) {
			String sql = "delete from TMS_UPLOAD_FILE_STORAGE where id=?";
			dao.deleteDataRowBySql(sql,new Object[] {id});
		}
	}

	/**
	 * 根据多个查询条件执行分页查询 
	 * 建议此方法仅用于返回数据共页面Table展示数据使用。不要用于业务逻辑处理。
	 * @param queryFields	查询条件
	 * @param pageIndex	  页数 从1开始
	 * @param pageSize	   每页显示的数据条数,负数表示不分页
	 * @param sortField	  排序字段
	 * @param sortOrder	  排序方式 asc desc
	 * @return PageModel
	 * @throws Exception
	 * @reviewer
	 */
	public QueryResult<UploadFileStorage> getUploadFileStorageExList(List<FilterColumn> queryFields,
			int pageIndex, int pageSize, String sortField, String sortOrder) throws Exception {
		
		QuerySQL querySql = new QuerySQL();
		querySql.setQueryColumns("t1.ID,t1.STORAGE_NAME,t1.STORAGE_PATH,t1.STORAGE_SIZE,t1.USABLE_STATUS,\r\n" + 
				"t1.REMARKS,t1.DISPLAY_ORDINAL,t1.RECORD_CREATE_USER,t1.RECORD_UPDATE_USER,t1.RECORD_CREATE_TIME,\r\n" + 
				"t1.RECORD_UPDATE_TIME,t1.RECORD_EDIT_FLAG");
		querySql.setTables("TMS_UPLOAD_FILE_STORAGE t1");
		
		if(queryFields != null) {
			//给列名指定表限定名
			for(FilterColumn field : queryFields) {
				String propName = field.getColumnName();
				String columnName = propName;
				if(!columnName.contains(".")) {
					columnName = "t1."+columnName;
					field.setColumnName(columnName);
				}
			}
		}

		if(StringUtils.isNotEmpty(sortField) && !sortField.contains(".")){ //特殊约定：如果sortField包含.号，则不对其进行处理，如 t1.CATALOG_NAME,t1.NAME
			if(!sortField.contains(".")) {
				sortField = "t1." + sortField;
			}
		}

		if(sortOrder == null) sortOrder = "";

		//处理oracle排序不稳定的问题（排序字段不是唯一性字段时，分页会有问题，多页的数据是一样的）
		if(StringUtils.isNotEmpty(sortField)){
			sortField = sortField+ " " + sortOrder +",t1.RECORD_UPDATE_TIME DESC,t1.ID";//保证排序字段的唯一性
		}else{
			sortField = "t1.RECORD_UPDATE_TIME DESC,t1.ID";//保证排序字段的唯一性
		}
		
		querySql.setOrderby(sortField);
		querySql.addQueryFilter(queryFields);

		QueryResult<UploadFileStorage> queryEntities = dao.queryEntities(querySql, new Pagination(pageIndex, pageSize), new IDataRowMapper<UploadFileStorage>() {

			@Override
			public UploadFileStorage translate(Map<String, Object> row) {
				RowMapper rowmap = new RowMapper(row);
				UploadFileStorage uploadFileStorage = new UploadFileStorage();
				uploadFileStorage.setId(rowmap.getString("ID"));
				uploadFileStorage.setStorageName(rowmap.getString("STORAGE_NAME"));
				uploadFileStorage.setStoragePath(rowmap.getString("STORAGE_PATH"));
				uploadFileStorage.setStorageSize(rowmap.getInteger("STORAGE_SIZE"));
				uploadFileStorage.setUsableStatus(rowmap.getInteger("USABLE_STATUS"));
				uploadFileStorage.setRemarks(rowmap.getString("REMARKS"));
				uploadFileStorage.setDisplayOrdinal(rowmap.getInteger("DISPLAY_ORDINAL"));
				uploadFileStorage.setRecordCreateUser(rowmap.getString("RECORD_CREATE_USER"));
				uploadFileStorage.setRecordUpdateUser(rowmap.getString("RECORD_UPDATE_USER"));
				uploadFileStorage.setRecordCreateTime(rowmap.getDate("RECORD_CREATE_TIME"));
				uploadFileStorage.setRecordUpdateTime(rowmap.getDate("RECORD_UPDATE_TIME"));
				uploadFileStorage.setRecordEditFlag(rowmap.getInteger("RECORD_EDIT_FLAG"));
				return uploadFileStorage;
			}});
		
		return queryEntities;
	}

	public List<UploadFileStorage> getAllUploadFileStorages() throws Exception{
		List<UploadFileStorage> fileStorages = new ArrayList<>();
		String sql = "select " + SelectFields + " from TMS_UPLOAD_FILE_STORAGE order by STORAGE_NAME";
		List<Map<String, Object>> rows = dao.queryDataRowMapList(sql);
		for(Map<String, Object> row : rows) {
			UploadFileStorage fileStorage = this.parseFrom(row);
			fileStorages.add(fileStorage);
		}
		return fileStorages;
	}

	public List<UploadFileStorage> getAllWriteableUploadFileStorages() throws Exception{
		List<UploadFileStorage> fileStorages = new ArrayList<>();
		String sql = "select ID,STORAGE_NAME,STORAGE_PATH,STORAGE_SIZE,USABLE_STATUS "
				+ " from TMS_UPLOAD_FILE_STORAGE where USABLE_STATUS='1' order by STORAGE_NAME";
		List<Map<String, Object>> rows = dao.queryDataRowMapList(sql);
		for(Map<String, Object> row : rows) {
			UploadFileStorage item = new UploadFileStorage();
			RowMapper mapper = new RowMapper(row);
			item.setId(mapper.getString("ID"));
			item.setStorageName(mapper.getString("STORAGE_NAME"));
			item.setStoragePath(mapper.getString("STORAGE_PATH"));
			item.setStorageSize(mapper.get_long("STORAGE_SIZE"));
			item.setUsableStatus(mapper.getInt("USABLE_STATUS"));
			fileStorages.add(item);
		}
		return fileStorages;
	}

	private UploadFileStorage parseFrom(Map<String, Object> row) {
		UploadFileStorage item = new UploadFileStorage();
		RowMapper mapper = new RowMapper(row);
		item.setId(mapper.getString("ID"));
		item.setStorageName(mapper.getString("STORAGE_NAME"));
		item.setStoragePath(mapper.getString("STORAGE_PATH"));
		item.setStorageSize(mapper.get_long("STORAGE_SIZE"));
		item.setUsableStatus(mapper.getInt("USABLE_STATUS"));
		item.setRemarks(mapper.getString("REMARKS"));
		item.setDisplayOrdinal(mapper.getInt("DISPLAY_ORDINAL"));
		item.setRecordCreateUser(mapper.getString("RECORD_CREATE_USER"));
		item.setRecordUpdateUser(mapper.getString("RECORD_UPDATE_USER"));
		item.setRecordCreateTime(mapper.getDate("RECORD_CREATE_TIME"));
		item.setRecordUpdateTime(mapper.getDate("RECORD_UPDATE_TIME"));
		item.setRecordEditFlag(mapper.getInt("RECORD_EDIT_FLAG"));
		return item;
	}

	public UploadFileStorage saveOrUpdateUploadFileStorage(UploadFileStorage fileStorage) throws Exception {
		if(fileStorage == null)return null;
		String id = fileStorage.getId();
		if(StringUtils.isEmpty(id)) {//insert
			id = UUIDUtil.createCompressedUUID();
			String sql = "insert into TMS_UPLOAD_FILE_STORAGE(STORAGE_NAME,STORAGE_PATH,STORAGE_SIZE,USABLE_STATUS,REMARKS"
					+ ",DISPLAY_ORDINAL,RECORD_CREATE_USER,RECORD_UPDATE_USER,RECORD_CREATE_TIME,RECORD_UPDATE_TIME"
					+ ",RECORD_EDIT_FLAG,ID) values(?,?,?,?,? ,?,?,?,?,? ,?,?)";
			dao.insertDataRowBySql(sql, new Object[] {
					fileStorage.getStorageName(),
					fileStorage.getStoragePath(),
					fileStorage.getStorageSize(),
					fileStorage.getUsableStatus(),
					fileStorage.getRemarks(),

					fileStorage.getDisplayOrdinal(),
					fileStorage.getRecordCreateUser(),
					fileStorage.getRecordUpdateUser(),
					fileStorage.getRecordCreateTime(),
					fileStorage.getRecordUpdateTime(),

					fileStorage.getRecordEditFlag(),
					id
			});
			fileStorage.setId(id);
		}else {//update
			String sql = "update TMS_UPLOAD_FILE_STORAGE set "
					+ "STORAGE_NAME=?,STORAGE_PATH=?,STORAGE_SIZE=?,USABLE_STATUS=?,REMARKS=?"
					+ ",DISPLAY_ORDINAL=?,RECORD_CREATE_USER=?,RECORD_UPDATE_USER=?,RECORD_CREATE_TIME=?,RECORD_UPDATE_TIME=?"
					+ ",RECORD_EDIT_FLAG=? where ID=?";
			dao.insertDataRowBySql(sql, new Object[] {
					fileStorage.getStorageName(),
					fileStorage.getStoragePath(),
					fileStorage.getStorageSize(),
					fileStorage.getUsableStatus(),
					fileStorage.getRemarks(),

					fileStorage.getDisplayOrdinal(),
					fileStorage.getRecordCreateUser(),
					fileStorage.getRecordUpdateUser(),
					fileStorage.getRecordCreateTime(),
					fileStorage.getRecordUpdateTime(),

					fileStorage.getRecordEditFlag(),
					id
			});
		}
		return fileStorage;
	}
}
